package edu.sjtu.erplab.processmining.connection;


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class ReadSQL {
	private String database = "Mydb";
	private String JDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";//SQL数据库引擎
	private String connectDBWithWin="jdbc:sqlserver://127.0.0.1:1433;integratedSecurity=true;DatabaseName=" + database;//数据源，Windows登录
	private Statement stmt;
	private Connection con;
	private DatabaseMetaData dbmd;
	private ArrayList<String> column = new ArrayList();	
	private ArrayList<ArrayList<String>> log = new ArrayList();
	private ArrayList<String> note = new ArrayList();	
	private ResultSet rs;
	
	public ArrayList<ArrayList<String>> getLog() {
		return log;
	}

	public void setLog(ArrayList<ArrayList<String>> log) {
		this.log = log;
	}		
		
	public ResultSet getRs() {
		return rs;
	}

	public void setRs(ResultSet rs) {
		this.rs = rs;
	}

	public ArrayList<String> getColumn() {
		return column;
	}

	public void setColumn(ArrayList<String> column) {
		this.column = column;
	}

	public void login(){
		try {
	  		 Class.forName(JDriver);//加载数据库引擎，返回给定字符串名的类
	  	 }catch(ClassNotFoundException e){
	  		 //e.printStackTrace();
	  		 System.out.println("加载数据库引擎失败");
	  		 System.exit(0);
	  	 }     
	  	 System.out.println("数据库驱动成功");
	  
	  	 try {	  		 
	  		 con = DriverManager.getConnection(connectDBWithWin);//连接数据库对象，Windows登录
	  		 System.out.println("连接数据库成功");
	  		 stmt = con.createStatement();//创建SQL命令对象
	  		 dbmd = con.getMetaData();	  		
	  	 }catch(SQLException e) {
		  	 e.printStackTrace();
		  	 System.out.println("数据库连接错误");
		  	 System.exit(0);
		 }
	}
	
	public void read(String table) throws SQLException{//读取数据		 
 		 System.out.println("开始读取 " + table + " 的数据");
 		 String sql = "SELECT * FROM " + table;
 		 //String sql = "select count(*) from " + table + ".dbo.syscolumns where id = object_id('表名')";
 		 rs=stmt.executeQuery(sql);//返回SQL语句查询结果集(集合)
 		 //循环输出每一条记录
 		 for (int i=0;i<column.size();i++){
 			System.out.print(column.get(i) + "\t"); 
 		 }
 		 System.out.println(" ");	
 		 while(rs.next()){
 			 //输出每个字段
 			for (int i=0;i<column.size();i++){			 
 				note.add(rs.getString(column.get(i)));
 				System.out.print(rs.getString(column.get(i))); 				
 			} 
 			log.add(note);
 			note = new ArrayList();
 			System.out.println(" ");	
 		 } 		 
 		 System.out.println("读取完毕");		
	}	
	
	public void readColumn(String table) throws SQLException{
		System.out.println("开始读取 " + table + " 的列名");
		rs = dbmd.getColumns(null, null, table, "%");			
		while(rs.next()){	
			column.add(rs.getString(4));
			System.out.println(rs.getString(4));	    	 
	     }		
		System.out.println(" ");
	}	
	
	public void close() throws SQLException{
		stmt.close();//关闭命令对象连接
 		con.close();//关闭数据库连接
	}
	
	public static void main(String[] args) {	  	 
		 ReadSQL newConnect = new ReadSQL();
		 newConnect.login();
		 try {						
			newConnect.readColumn("TEST");
			newConnect.read("TEST");
			newConnect.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 
	 }
}
